﻿Imports System.Data
Imports System.Data.SqlClient
Imports Sistema_InformacionNaviera.BE
Imports Sistema_InformacionNavier.UTIL

Public Class UbigeoDALC

    Public Function ListaPais() As List(Of UbigeoBE)

        Dim sqlListar As String
        Dim objUbigeo As New List(Of UbigeoBE)
        Dim objProvBE As UbigeoBE = Nothing
        'Dim arrSqlParameter(0) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim blnExito As Boolean
        Dim strMensaje As String = String.Empty
        Dim intI As Integer = 0

        Try

            sqlListar = "Util_Paises"

            ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, Nothing)
            dt = ds.Tables(0)

            If dt.Rows.Count > 0 Then

                While intI < dt.Rows.Count
                    objProvBE = New UbigeoBE()
                    objProvBE.CodPai = dt.Rows(intI).ItemArray(0).ToString
                    objProvBE.DesPai = dt.Rows(intI).ItemArray(1).ToString
                    intI = intI + 1

                    objUbigeo.Add(objProvBE)
                End While
            End If
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return objUbigeo
    End Function


    Public Function ListaDpto(ByVal objUbigeoBE As UbigeoBE) As List(Of UbigeoBE)

        Dim sqlListar As String
        Dim objUbigeo As New List(Of UbigeoBE)
        Dim objProvBE As UbigeoBE = Nothing
        Dim arrSqlParameter(0) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim blnExito As Boolean
        Dim strMensaje As String = String.Empty
        Dim intI As Integer = 0

        Try

            sqlListar = "Util_PaisesDepartamentos"


            arrSqlParameter(0) = New SqlParameter("@KPAI", System.Data.SqlDbType.VarChar)
            arrSqlParameter(0).Value = objUbigeoBE.CodPai
            objUbigeo = New List(Of UbigeoBE)()

            ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, arrSqlParameter)
            dt = ds.Tables(0)

            If dt.Rows.Count > 0 Then

                While intI < dt.Rows.Count
                    objProvBE = New UbigeoBE()
                    objProvBE.CodDpto = dt.Rows(intI).ItemArray(0).ToString
                    objProvBE.DesDpto = dt.Rows(intI).ItemArray(1).ToString
                    intI = intI + 1

                    objUbigeo.Add(objProvBE)
                End While
            End If
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return objUbigeo
    End Function

    Public Function ListaProvincia(ByVal objUbigeoBE As UbigeoBE) As List(Of UbigeoBE)

        Dim sqlListar As String
        Dim objUbigeo As New List(Of UbigeoBE)
        Dim objProvBE As UbigeoBE = Nothing
        Dim arrSqlParameter(0) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim blnExito As Boolean
        Dim strMensaje As String = String.Empty
        Dim intI As Integer = 0

        Try

            sqlListar = "Util_PaisesDepartamentosProvincias"


            arrSqlParameter(0) = New SqlParameter("@KDEP", System.Data.SqlDbType.VarChar)
            arrSqlParameter(0).Value = objUbigeoBE.CodDpto 

            ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, arrSqlParameter)
            dt = ds.Tables(0)

            If dt.Rows.Count > 0 Then

                While intI < dt.Rows.Count
                    objProvBE = New UbigeoBE()
                    objProvBE.CodProv = dt.Rows(intI).ItemArray(0).ToString
                    objProvBE.DesProv = dt.Rows(intI).ItemArray(1).ToString
                    intI = intI + 1

                    objUbigeo.Add(objProvBE)
                End While
            End If
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return objUbigeo
    End Function
End Class
